package dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import dao.OyxVipUser_IntegralDao;
import entity.Order_Master;
import entity.OyxVipLevelShow;
import entity.User;
import entity.User_Inf;
import entity.User_Point;
import util.C3P0Util;


public class OyxVipUser_IntegralDaoImpl implements OyxVipUser_IntegralDao{
	
	//设计方法用于返回操作数据库的qr对象
	private QueryRunner getQueryRunner(){
		return new QueryRunner(C3P0Util.getDataSource());
	}
	//通过用户id查询用户积分方法
	@Override
	public Integer getIndentIntegral(Integer user_id) {
		QueryRunner qr = this.getQueryRunner();
		Integer order_point = 0;
		String sql = "SELECT * FROM `order_master` WHERE `order_master`.`user_id`=?;";
		try {
			Order_Master order_Master = qr.query(sql,user_id, new BeanHandler<Order_Master>(Order_Master.class));
			order_point = order_Master.getOrder_point();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return order_point;
	}
	
	
	//通过用户ID修改    user_inf表   的订单积分(原有积分＋订单积分)
	@Override
	public int updateIndentIntegral(Integer user_id,Integer order_point) {
		QueryRunner qr = this.getQueryRunner();
		int i = 0;
		String sql = "UPDATE `user_inf` SET `user_inf`.`user_point`= ? + `user_inf`.`user_point`  WHERE  `user_inf`.`userId`=?;";
		Object[] params = {order_point,user_id};
		try {
		i = qr.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	
	//通过积分对象给积分表添加数据
	@Override
	public int addUser_Point(User_Point user_Point) {
		QueryRunner qr = this.getQueryRunner();
		int i = 0;
		String sql = "INSERT INTO `user_point` VALUES(NULL,?,?,?,?)";
		Object[] params = {user_Point.getUser_id(),user_Point.getRefer_number(),user_Point.getChange_point(),user_Point.getCreate_time()};
		 try {
			i = qr.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	
	//通过用户ID查询  User_Inf表  的用户对象信息
	@Override
	public User_Inf getUser_InfMessage(Integer userId) {
		QueryRunner qr = this.getQueryRunner();
		User_Inf user_Inf=null;
		String sql = "SELECT * FROM user_inf WHERE userId=?";
		try {
		user_Inf = qr.query(sql,userId,new BeanHandler<User_Inf>(User_Inf.class));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return user_Inf;
	}
	
	//查询会员id返回会员总数
	@Override
	public Integer getVipUser_InfSum() {
		QueryRunner qr =  new QueryRunner(C3P0Util.getDataSource());
		Long vipSum = null;
		String sql = "SELECT COUNT(userId) FROM user_inf;";
		try {
			vipSum = (Long) qr.query(sql, new ScalarHandler(1));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vipSum.intValue();
	}
	
	//查询会员等级为level的数量
	@Override
	public Integer getVipUser_InfSumLevel(Integer level) {
		QueryRunner qr = this.getQueryRunner();
		Long vipSumLevel = null;
		String sql = "SELECT COUNT(userId) FROM user_inf WHERE user_level= ?;";
		try {
			vipSumLevel = (Long) qr.query(sql,level,new ScalarHandler(1));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vipSumLevel.intValue();
	}
	
	//根据会员等级判断返回的会员展示数据
	@Override
	public List<OyxVipLevelShow> getVipUserLevelShow(Integer vipLevel,Integer pageNo, Integer paeSize) {
		QueryRunner qr = this.getQueryRunner();
		String sql = "SELECT user_inf.`username`,user_inf.`user_point`,user_inf.`userId` FROM user_inf,user_level_inf WHERE  user_inf.`user_level`=user_level_inf.`user_level` AND user_inf.user_level=?  LIMIT ?,?;";
		List<OyxVipLevelShow> query = null;
		try {
		  query = qr.query(sql,new Integer[]{vipLevel,(pageNo-1)*paeSize,paeSize},new BeanListHandler<OyxVipLevelShow>(OyxVipLevelShow.class));
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return query;
	}
	

}
